package o2o.config.dao.utils;

import java.util.List;

/**
 * 处理配置文件中的sql语句
 * ClassName: SqlUtils
 *
 * @author Jahnke
 * @Description: TODO
 * @date 2016年6月18日
 */
public class DaoUtils {
    public static String UserPwd = "UserPwd";    //查询所有用户帐号密码
    public static String UserInfo = "UserInfo";    //查询用户信息

    public static String getSql(String tableName, QueryCondition query) {
        StringBuffer buf = new StringBuffer("SELECT * FROM ").append(tableName);
        StringBuffer sql = new StringBuffer();
        sql.append(getWhereSql(buf.toString(), query));

        return sql.toString();
    }

    //获取总个数 即去掉limit
    public static String getSqlCount(String tableName, QueryCondition query) {
        StringBuffer buf = new StringBuffer("SELECT count(1) counts FROM ").append(tableName);
        StringBuffer sql = new StringBuffer();
        sql.append(getWhereSqlCount(buf.toString(), query));
        return sql.toString();
    }


    public static StringBuffer getWhereSql(String sql, QueryCondition query) {
        StringBuffer buf = new StringBuffer(sql);
        //判断查询条件
        if (query.getExpressions() != null && query.getExpressions().size() > 0) {
            List<Expression> list = query.getExpressions();
            if (list != null && !list.isEmpty()) {
                buf.append(" WHERE ");
            }
            for (int i = 0; i < list.size(); i++) {
                if (i > 0) {
                    buf.append(Expression.PRE_AND);
                }
                Expression ex = list.get(i);
                if (ex.getOp().equals("=") || ex.getOp().equals("!=")
                        || ex.getOp().equals(">") || ex.getOp().equals("<")
                        || ex.getOp().equals(">=") || ex.getOp().equals("<=")) {
                    buf.append(" ").append(ex.getName()).append(ex.getOp()).append("'").append(ex.getValue()).append("' ");
                } else if (ex.getOp().equals("rlike")) {
                    buf.append(" ").append(ex.getName()).append(" like '").append(ex.getValue()).append("%' ");
                } else if (ex.getOp().equals("llike")) {
                    buf.append(" ").append(ex.getName()).append(" like '%").append(ex.getValue()).append("' ");
                } else if (ex.getOp().equals("like")) {
                    buf.append(" ").append(ex.getName()).append(" like '%").append(ex.getValue()).append("%' ");
                } else if (ex.getOp().equals("in") || ex.getOp().equals("not in")) {
                    buf.append(" ").append(ex.getName()).append(" ").append(ex.getOp()).append("(").append(ex.getValue()).append(") ");
                } else if (ex.getOp().equals("is null") || ex.getOp().equals("is not null")) {
                    buf.append(" ").append(ex.getName()).append(" ").append(ex.getOp()).append(" ");
                }
            }
        }

        //排序
        if (query.getOrders() != null && query.getOrders().size() > 0) {
            buf.append(" ORDER BY ");
            List<Order> orders = query.getOrders();
            for (int i = 0; i < orders.size(); i++) {
                Order order = orders.get(i);
                if (i == orders.size() - 1) {
                    buf.append(order.getName()).append(" ").append(order.getOrder()).append(" ");
                } else {
                    buf.append(order.getName()).append(" ").append(order.getOrder()).append(",");
                }
            }
        }

        //分页
        if (query.getPage() != null && query.getPage() > 0 && query.getSize() != null && query.getSize() > 0) {
            buf.append(" LIMIT ").append(query.getSize() * (query.getPage() - 1)).append(",").append(query.getSize());
        }

        return buf;
    }

    // 获取总个数，即去掉limit
    public static StringBuffer getWhereSqlCount(String sql, QueryCondition query) {
        StringBuffer buf = new StringBuffer(sql);
        //判断查询条件
        if (query.getExpressions() != null && query.getExpressions().size() > 0) {
            List<Expression> list = query.getExpressions();
            if (list != null && !list.isEmpty()) {
                buf.append(" WHERE ");
            }
            for (int i = 0; i < list.size(); i++) {
                Expression ex = list.get(i);
                if (i > 0) {
                    buf.append(Expression.PRE_AND);
                }
                if (ex.getOp().equals("=") || ex.getOp().equals("!=")
                        || ex.getOp().equals(">") || ex.getOp().equals("<")
                        || ex.getOp().equals(">=") || ex.getOp().equals("<=")) {
                    buf.append(" ").append(ex.getName()).append(ex.getOp()).append("'").append(ex.getValue()).append("' ");
                } else if (ex.getOp().equals("rlike")) {
                    buf.append(" ").append(ex.getName()).append(" like '").append(ex.getValue()).append("%' ");
                } else if (ex.getOp().equals("llike")) {
                    buf.append(" ").append(ex.getName()).append(" like '%").append(ex.getValue()).append("' ");
                } else if (ex.getOp().equals("like")) {
                    buf.append(" ").append(ex.getName()).append(" like '%").append(ex.getValue()).append("%' ");
                } else if (ex.getOp().equals("in") || ex.getOp().equals("not in")) {
                    buf.append(" ").append(ex.getName()).append(" ").append(ex.getOp()).append("(").append(ex.getValue()).append(") ");
                } else if (ex.getOp().equals("is null") || ex.getOp().equals("is not null")) {
                    buf.append(" ").append(ex.getName()).append(" ").append(ex.getOp()).append(" ");
                }
            }
        }

        //排序
        if (query.getOrders() != null && query.getOrders().size() > 0) {
            buf.append(" ORDER BY ");
            List<Order> orders = query.getOrders();
            for (int i = 0; i < orders.size(); i++) {
                Order order = orders.get(i);
                if (i == orders.size() - 1) {
                    buf.append(order.getName()).append(" ").append(order.getOrder()).append(" ");
                } else {
                    buf.append(order.getName()).append(" ").append(order.getOrder()).append(",");
                }
            }
        }

        return buf;
    }

    public static void main(String[] args) {
//        QueryCondition query = QueryCondition.getInstance();
//        Expression exp = Expression.getInstance();
//        exp.addChild("hehe", "sd").addChild("asda", "sd").addChild("or", "username", Expression.OP_IN, "2,3")
//                .addChild("and", "username", Expression.OP_RLIKE, "23").addChild("and", "username", Expression.OP_LLIKE, "s")
//                .addChild("and", "username", Expression.OP_LIKE, "sds").addChild("and", "username", Expression.OP_ISNOTNULL, "sds");
//        query.addCondition(exp);
//        query.addOrders("asd", Order.ASC);
//        System.out.println(getSql(UserInfo, query));

//        Integer type = 1;
//        QueryCondition query = new QueryCondition();
//        query.addCondition(Expression.getInstance().addChild("delFlag", 0).addChild("username", "18560126362"));
//        if (type != null) {
//            query.addCondition(Expression.getInstance().addChild("type", type));
//        }
//        String sql = DaoUtils.getSql(PojoTables.USER_ROLE_AUTH_INFO, query);
//        System.out.println(sql);
    }
}
